package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;

import dto.StudentResultDto;

public class StudentResultDao {

	public String insertStudentResult(StudentResultDto studentResult) {

		if (studentResult == null)
			return "Insert failed! Input data is null.";
		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "INSERT INTO student_result "
				+ "(result_desc, marks, comments, fk_quiz_no, student_user_id) "
				+ "VALUES (?,?,?,?,?) "
				+ " on duplicate key update result_desc=values(result_desc)"
				+ ", marks=values(marks)" + ",comments=values(comments)"
				+ ",fk_quiz_no=values(fk_quiz_no)"
				+ ",student_user_id=values(student_user_id)";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			// preparedStatement.setInt(1, studentResult.getResultId());
			preparedStatement.setString(1, studentResult.getResultDesc());
			preparedStatement.setString(2, studentResult.getMark());
			preparedStatement.setString(3, studentResult.getComment());
			preparedStatement.setInt(4, studentResult.getQuizNo());
			preparedStatement.setString(5, studentResult.getStudentId());

			preparedStatement.execute();
			response = "Inserted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Insert failed! " + e.getMessage();
		}

		return response;
	}

	public ArrayList<StudentResultDto> getAllStudentResult() {

		ArrayList<StudentResultDto> listStudentResult = new ArrayList<>();

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT * FROM student_result";

		Statement statement;
		try {
			statement = connection.createStatement();

			ResultSet resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				StudentResultDto studentResult = new StudentResultDto();
				studentResult.setResultDesc(resultSet.getString("result_desc"));
				studentResult.setMark(resultSet.getString("marks"));
				studentResult.setComment(resultSet.getString("comments"));
				studentResult.setQuizNo(resultSet.getInt("fk_quiz_no"));
				studentResult.setStudentId(resultSet
						.getString("student_user_id"));
				listStudentResult.add(studentResult);
			}

			statement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listStudentResult;
	}

}
